const express = require('express');
const cors = require('cors');
const path = require('path');
const { Pool } = require('pg');
require('dotenv').config({ path: path.join(__dirname, '../.env') });

// 创建两个Express应用
const apiApp = express();
const frontendApp = express();

const apiPort = process.env.BACKEND_PORT || 9081;
const frontendPort = process.env.FRONT_PORT || 9080;

// API应用中间件
apiApp.use(cors({
  origin: [
    'http://localhost:3000',  // 本地开发环境
    'http://localhost:9080',  // 本地前端服务
    'http://localhost:9081',  // 本地API服务
    'http://localhost:9080',  // 远程前端服务
    'http://localhost:9081',  // 远程API服务
    /^http:\/\/.*$/  // 开发环境允许所有HTTP请求
  ],
  credentials: true
}));
apiApp.use(express.json());

// 前端应用中间件
frontendApp.use(cors());

// 在生产环境中提供静态文件服务
if (process.env.NODE_ENV === 'production') {
  // 前端应用提供 React 构建的静态文件
  frontendApp.use(express.static(path.join(__dirname, '../build')));
}

// PostgreSQL 连接池
const pool = new Pool({
  user: process.env.DB_USERNAME,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
  connectionTimeoutMillis: 10000, // 10秒连接超时
  idleTimeoutMillis: 30000, // 30秒空闲超时
  max: 20, // 最大连接数
});

// 数据库连接函数（带重试机制）
async function connectToDatabase(retries = 5) {
  // 调试信息：显示连接参数
  console.log('📡 数据库连接参数:');
  console.log(`   主机: ${process.env.DB_HOST}`);
  console.log(`   端口: ${process.env.DB_PORT}`);
  console.log(`   数据库: ${process.env.DB_DATABASE}`);
  console.log(`   用户名: ${process.env.DB_USERNAME}`);
  console.log(`   密码: ${process.env.DB_PASSWORD ? '***' : '未设置'}`);

  for (let i = 0; i < retries; i++) {
    try {
      console.log(`🔄 尝试连接数据库... (${i + 1}/${retries})`);
      const client = await pool.connect();
      console.log('✅ 数据库连接成功');

      // 初始化数据库表
      await initializeDatabase(client);

      client.release();
      return true;
    } catch (err) {
      console.error(`❌ 数据库连接失败 (尝试 ${i + 1}/${retries}):`, err.message);
      console.error(`   错误代码: ${err.code}`);

      if (i === retries - 1) {
        console.error('🚨 数据库连接重试次数已用完，服务器将继续启动但数据库功能不可用');
        return false;
      }

      // 等待2秒后重试
      await new Promise(resolve => setTimeout(resolve, 2000));
    }
  }
}

// 初始化数据库表
async function initializeDatabase(client) {
  try {
    console.log('🔧 检查并初始化数据库表...');

    // 检查表是否存在
    const tablesExist = await client.query(`
      SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_name = 'works'
      );
    `);

    if (!tablesExist.rows[0].exists) {
      console.log('📋 数据库表不存在，开始创建...');

      // 读取并执行数据库初始化脚本
      const fs = require('fs');
      const path = require('path');
      const sqlScript = fs.readFileSync(path.join(__dirname, 'database.sql'), 'utf8');

      await client.query(sqlScript);
      console.log('✅ 数据库表创建完成');
    } else {
      console.log('✅ 数据库表已存在，跳过初始化');
    }
  } catch (err) {
    console.error('❌ 数据库表初始化失败:', err.message);
    // 不抛出错误，让服务器继续启动
  }
}

// API 路由

// 1. 获取所有作品（支持分页和课程筛选）
apiApp.get('/api/works', async (req, res) => {
  try {
    const { page = 1, limit = 10, courseId } = req.query;
    const offset = (page - 1) * limit;

    let whereClause = '';
    let queryParams = [];

    if (courseId) {
      whereClause = 'WHERE w.course_id = $1';
      queryParams = [courseId, limit, offset];
    } else {
      queryParams = [limit, offset];
    }

    const result = await pool.query(`
      SELECT 
        w.id,
        w.title,
        w.creator,
        w.cover_url as "coverUrl",
        w.url,
        w.source_url as "sourceUrl",
        w.description,
        w.tags,
        w.category,
        w.course_id as "courseId",
        c.name as "courseName",
        w.created_at as "createdAt",
        w.updated_at as "updatedAt"
      FROM works w
      LEFT JOIN courses c ON w.course_id = c.id
      ${whereClause}
      ORDER BY w.created_at DESC
      LIMIT $${courseId ? 2 : 1} OFFSET $${courseId ? 3 : 2}
    `, queryParams);

    // 获取总数
    const countResult = await pool.query(`
      SELECT COUNT(*) as total
      FROM works w
      ${whereClause}
    `, courseId ? [courseId] : []);

    res.json({
      works: result.rows,
      total: parseInt(countResult.rows[0].total),
      page: parseInt(page),
      limit: parseInt(limit),
      totalPages: Math.ceil(countResult.rows[0].total / limit)
    });
  } catch (err) {
    console.error('获取作品列表失败:', err);
    res.status(500).json({ error: '获取作品列表失败' });
  }
});

// 2. 根据ID获取作品
apiApp.get('/api/works/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await pool.query(`
      SELECT 
        w.id,
        w.title,
        w.creator,
        w.cover_url as "coverUrl",
        w.url,
        w.source_url as "sourceUrl",
        w.description,
        w.tags,
        w.category,
        w.course_id as "courseId",
        c.name as "courseName",
        w.created_at as "createdAt",
        w.updated_at as "updatedAt"
      FROM works w
      LEFT JOIN courses c ON w.course_id = c.id
      WHERE w.id = $1
    `, [id]);

    if (result.rows.length === 0) {
      return res.status(404).json({ error: '作品不存在' });
    }

    res.json(result.rows[0]);
  } catch (err) {
    console.error('获取作品失败:', err);
    res.status(500).json({ error: '获取作品失败' });
  }
});

// 3. 添加作品
apiApp.post('/api/works', async (req, res) => {
  try {
    const { title, creator, coverUrl, url, sourceUrl, description, tags, category, courseId } = req.body;

    // 验证必填字段
    if (!title || !creator) {
      return res.status(400).json({ error: '标题和创作者为必填字段' });
    }

    const result = await pool.query(`
      INSERT INTO works (title, creator, cover_url, url, source_url, description, tags, category, course_id)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
      RETURNING 
        id,
        title,
        creator,
        cover_url as "coverUrl",
        url,
        source_url as "sourceUrl",
        description,
        tags,
        category,
        course_id as "courseId",
        created_at as "createdAt",
        updated_at as "updatedAt"
    `, [title, creator, coverUrl, url, sourceUrl, description, tags || [], category, courseId]);

    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error('添加作品失败:', err);
    res.status(500).json({ error: '添加作品失败' });
  }
});

// 4. 更新作品
apiApp.put('/api/works/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { title, creator, coverUrl, url, sourceUrl, description, tags, category, courseId } = req.body;

    // 验证必填字段
    if (!title || !creator) {
      return res.status(400).json({ error: '标题和创作者为必填字段' });
    }

    const result = await pool.query(`
      UPDATE works 
      SET 
        title = $1,
        creator = $2,
        cover_url = $3,
        url = $4,
        source_url = $5,
        description = $6,
        tags = $7,
        category = $8,
        course_id = $9,
        updated_at = NOW()
      WHERE id = $10
      RETURNING 
        id,
        title,
        creator,
        cover_url as "coverUrl",
        url,
        source_url as "sourceUrl",
        description,
        tags,
        category,
        course_id as "courseId",
        created_at as "createdAt",
        updated_at as "updatedAt"
    `, [title, creator, coverUrl, url, sourceUrl, description, tags || [], category, courseId, id]);

    if (result.rows.length === 0) {
      return res.status(404).json({ error: '作品不存在' });
    }

    res.json(result.rows[0]);
  } catch (err) {
    console.error('更新作品失败:', err);
    res.status(500).json({ error: '更新作品失败' });
  }
});

// 5. 删除作品
apiApp.delete('/api/works/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await pool.query('DELETE FROM works WHERE id = $1', [id]);

    if (result.rowCount === 0) {
      return res.status(404).json({ error: '作品不存在' });
    }

    res.json({ success: true, message: '作品删除成功' });
  } catch (err) {
    console.error('删除作品失败:', err);
    res.status(500).json({ error: '删除作品失败' });
  }
});

// 6. 搜索作品
apiApp.get('/api/works/search/:term', async (req, res) => {
  try {
    const { term } = req.params;

    if (!term) {
      return res.json([]);
    }

    const searchTerm = `%${term.toLowerCase()}%`;
    const result = await pool.query(`
      SELECT 
        w.id,
        w.title,
        w.creator,
        w.cover_url as "coverUrl",
        w.url,
        w.source_url as "sourceUrl",
        w.description,
        w.tags,
        w.category,
        w.course_id as "courseId",
        c.name as "courseName",
        w.created_at as "createdAt",
        w.updated_at as "updatedAt"
      FROM works w
      LEFT JOIN courses c ON w.course_id = c.id
      WHERE 
        LOWER(w.title) LIKE $1 OR 
        LOWER(w.creator) LIKE $1 OR 
        LOWER(w.description) LIKE $1 OR 
        LOWER(w.category) LIKE $1 OR
        LOWER(c.name) LIKE $1 OR
        EXISTS (
          SELECT 1 FROM unnest(w.tags) AS tag 
          WHERE LOWER(tag) LIKE $1
        )
      ORDER BY w.created_at DESC
    `, [searchTerm]);

    res.json(result.rows);
  } catch (err) {
    console.error('搜索失败:', err);
    res.status(500).json({ error: '搜索失败' });
  }
});

// 7. 批量删除所有作品
apiApp.delete('/api/works', async (req, res) => {
  try {
    await pool.query('DELETE FROM works');
    res.json({ success: true, message: '所有作品已删除' });
  } catch (err) {
    console.error('批量删除失败:', err);
    res.status(500).json({ error: '批量删除失败' });
  }
});

// 8. 批量导入作品
apiApp.post('/api/works/import', async (req, res) => {
  try {
    const { works } = req.body;

    if (!Array.isArray(works)) {
      return res.status(400).json({ error: '无效的数据格式' });
    }

    // 先清空现有数据
    await pool.query('DELETE FROM works');

    // 批量插入新数据
    const insertPromises = works.map(work => {
      const { title, creator, coverUrl, url, sourceUrl, description, tags, category, courseId } = work;
      return pool.query(`
        INSERT INTO works (title, creator, cover_url, url, source_url, description, tags, category, course_id)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
      `, [title, creator, coverUrl, url, sourceUrl, description, tags || [], category, courseId]);
    });

    await Promise.all(insertPromises);

    res.json({ success: true, message: `成功导入 ${works.length} 个作品` });
  } catch (err) {
    console.error('导入失败:', err);
    res.status(500).json({ error: '导入失败' });
  }
});

// 管理员相关API

// 9. 管理员登录
apiApp.post('/api/admin/login', async (req, res) => {
  try {
    const { username, password } = req.body;

    if (!username || !password) {
      return res.status(400).json({ error: '用户名和密码为必填字段' });
    }

    const result = await pool.query(`
      SELECT id, username FROM admins 
      WHERE username = $1 AND password = $2
    `, [username, password]);

    if (result.rows.length === 0) {
      return res.status(401).json({ error: '用户名或密码错误' });
    }

    res.json({
      success: true,
      admin: result.rows[0],
      message: '登录成功'
    });
  } catch (err) {
    console.error('管理员登录失败:', err);
    res.status(500).json({ error: '登录失败' });
  }
});

// 10. 获取所有管理员
apiApp.get('/api/admins', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT id, username, created_at as "createdAt"
      FROM admins 
      ORDER BY created_at DESC
    `);
    res.json(result.rows);
  } catch (err) {
    console.error('获取管理员列表失败:', err);
    res.status(500).json({ error: '获取管理员列表失败' });
  }
});

// 课程相关API

// 11. 获取所有课程
apiApp.get('/api/courses', async (req, res) => {
  try {
    const { adminId } = req.query;

    let whereClause = '';
    let queryParams = [];

    if (adminId) {
      whereClause = 'WHERE c.admin_id = $1';
      queryParams = [adminId];
    }

    const result = await pool.query(`
      SELECT 
        c.id,
        c.name,
        c.teacher,
        c.description,
        c.cover_image as "coverImage",
        c.admin_id as "adminId",
        a.username as "adminUsername",
        c.created_at as "createdAt",
        c.updated_at as "updatedAt",
        COUNT(w.id) as "worksCount"
      FROM courses c
      LEFT JOIN admins a ON c.admin_id = a.id
      LEFT JOIN works w ON c.id = w.course_id
      ${whereClause}
      GROUP BY c.id, a.username
      ORDER BY c.created_at DESC
    `, queryParams);

    res.json(result.rows);
  } catch (err) {
    console.error('获取课程列表失败:', err);
    res.status(500).json({ error: '获取课程列表失败' });
  }
});

// 12. 根据ID获取课程
apiApp.get('/api/courses/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await pool.query(`
      SELECT 
        c.id,
        c.name,
        c.teacher,
        c.description,
        c.cover_image as "coverImage",
        c.admin_id as "adminId",
        a.username as "adminUsername",
        c.created_at as "createdAt",
        c.updated_at as "updatedAt"
      FROM courses c
      LEFT JOIN admins a ON c.admin_id = a.id
      WHERE c.id = $1
    `, [id]);

    if (result.rows.length === 0) {
      return res.status(404).json({ error: '课程不存在' });
    }

    res.json(result.rows[0]);
  } catch (err) {
    console.error('获取课程失败:', err);
    res.status(500).json({ error: '获取课程失败' });
  }
});

// 13. 添加课程
apiApp.post('/api/courses', async (req, res) => {
  try {
    const { name, teacher, description, coverImage, adminId } = req.body;

    // 验证必填字段
    if (!name || !teacher || !adminId) {
      return res.status(400).json({ error: '课程名称、任课教师和管理员ID为必填字段' });
    }

    const result = await pool.query(`
      INSERT INTO courses (name, teacher, description, cover_image, admin_id)
      VALUES ($1, $2, $3, $4, $5)
      RETURNING 
        id,
        name,
        teacher,
        description,
        cover_image as "coverImage",
        admin_id as "adminId",
        created_at as "createdAt",
        updated_at as "updatedAt"
    `, [name, teacher, description, coverImage, adminId]);

    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error('添加课程失败:', err);
    res.status(500).json({ error: '添加课程失败' });
  }
});

// 14. 更新课程
apiApp.put('/api/courses/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, teacher, description, coverImage } = req.body;

    // 验证必填字段
    if (!name || !teacher) {
      return res.status(400).json({ error: '课程名称和任课教师为必填字段' });
    }

    const result = await pool.query(`
      UPDATE courses 
      SET 
        name = $1,
        teacher = $2,
        description = $3,
        cover_image = $4,
        updated_at = NOW()
      WHERE id = $5
      RETURNING 
        id,
        name,
        teacher,
        description,
        cover_image as "coverImage",
        admin_id as "adminId",
        created_at as "createdAt",
        updated_at as "updatedAt"
    `, [name, teacher, description, coverImage, id]);

    if (result.rows.length === 0) {
      return res.status(404).json({ error: '课程不存在' });
    }

    res.json(result.rows[0]);
  } catch (err) {
    console.error('更新课程失败:', err);
    res.status(500).json({ error: '更新课程失败' });
  }
});

// 15. 删除课程
apiApp.delete('/api/courses/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await pool.query('DELETE FROM courses WHERE id = $1', [id]);

    if (result.rowCount === 0) {
      return res.status(404).json({ error: '课程不存在' });
    }

    res.json({ success: true, message: '课程删除成功' });
  } catch (err) {
    console.error('删除课程失败:', err);
    res.status(500).json({ error: '删除课程失败' });
  }
});

// 16. 获取管理员的所有作品
apiApp.get('/api/admin/:adminId/works', async (req, res) => {
  try {
    const { adminId } = req.params;

    const result = await pool.query(`
      SELECT 
        w.id,
        w.title,
        w.creator,
        w.cover_url as "coverUrl",
        w.url,
        w.source_url as "sourceUrl",
        w.description,
        w.tags,
        w.category,
        w.course_id as "courseId",
        c.name as "courseName",
        w.created_at as "createdAt",
        w.updated_at as "updatedAt"
      FROM works w
      LEFT JOIN courses c ON w.course_id = c.id
      WHERE c.admin_id = $1
      ORDER BY w.created_at DESC
    `, [adminId]);

    res.json(result.rows);
  } catch (err) {
    console.error('获取管理员作品失败:', err);
    res.status(500).json({ error: '获取管理员作品失败' });
  }
});

// 健康检查
apiApp.get('/api/health', async (req, res) => {
  try {
    // 检查数据库连接
    await pool.query('SELECT 1');
    res.json({
      status: 'ok',
      message: 'API 服务正常运行',
      database: 'connected',
      timestamp: new Date().toISOString()
    });
  } catch (err) {
    res.status(503).json({
      status: 'error',
      message: 'API 服务异常',
      database: 'disconnected',
      error: err.message,
      timestamp: new Date().toISOString()
    });
  }
});

// 在生产环境中，前端应用的所有路由都返回React应用
if (process.env.NODE_ENV === 'production') {
  frontendApp.get('*', (req, res) => {
    res.sendFile(path.join(__dirname, '../build', 'index.html'));
  });
}

// 启动服务器函数
async function startServer() {
  console.log('🚀 启动服务器...');

  // 首先尝试连接数据库
  const dbConnected = await connectToDatabase();

  if (!dbConnected) {
    console.log('⚠️  数据库连接失败，但服务器将继续启动（数据库功能不可用）');
  }

  // 启动API服务器
  apiApp.listen(apiPort, '0.0.0.0', () => {
    console.log(`✅ API服务运行在 http://localhost:${apiPort}`);
  });

  // 只在生产环境启动前端服务器
  if (process.env.NODE_ENV === 'production') {
    frontendApp.listen(frontendPort, '0.0.0.0', () => {
      console.log(`✅ 前端服务运行在 http://localhost:${frontendPort}`);
    });
  } else {
    console.log(`✅ 前端服务运行在 http://localhost:3000 (React开发服务器)`);
  }

  console.log('🎉 服务器启动完成！');
}

// 优雅关闭
process.on('SIGINT', async () => {
  console.log('🛑 正在关闭服务器...');
  try {
    await pool.end();
    console.log('✅ 数据库连接已关闭');
  } catch (err) {
    console.error('❌ 关闭数据库连接时出错:', err.message);
  }
  process.exit(0);
});

// 启动服务器
startServer().catch(err => {
  console.error('🚨 服务器启动失败:', err);
  process.exit(1);
});
